﻿using System;
using System.Data;
using System.Data.SQLite;
using Taxhui.Utils.UserModel;

namespace Taxhui.Utils.DBUtils.SQLiteDB
{
    public class DB
    {
        private readonly SqliteDbConfig _cfg;
        public DB(SqliteDbConfig cfg)
        {
            _cfg = cfg;
        }
        #region 打开数据库
        private SQLiteConnection ConnDB()
        {
            try
            {
                var conn = new SQLiteConnection(_cfg.ConnStr);
                conn.Open();
                return conn;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion
        #region 关闭数据库连接
        public void CloseDB(SQLiteConnection conn)
        {
            try
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
                conn = null;
            }
            catch (Exception)
            {
            }
        }
        #endregion
        #region 检查空参数
        private void CheckParameter(ref SQLiteParameter[] op)
        {
            try
            {
                foreach (var item in op)
                {
                    if (item.Value == null)
                        item.Value = DBNull.Value;
                    else if (item.DbType == DbType.DateTime && string.IsNullOrEmpty(item.Value.ToString()))
                        item.Value = DBNull.Value;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion
        #region 查询数据库
        public DataTable Query(string sqlstr, SQLiteParameter[] op = null)
        {
            if (string.IsNullOrEmpty(sqlstr))
                throw new Exception("命令不能为空！");
            var conn = ConnDB();
            try
            {
                SQLiteDataAdapter da = new SQLiteDataAdapter(sqlstr, conn);
                if (op != null)
                {
                    CheckParameter(ref op);
                    da.SelectCommand.Parameters.AddRange(op);
                    if (da.InsertCommand != null)
                        da.InsertCommand.Parameters.AddRange(op);
                    if (da.DeleteCommand != null)
                        da.DeleteCommand.Parameters.AddRange(op);
                    if (da.UpdateCommand != null)
                        da.UpdateCommand.Parameters.AddRange(op);
                }
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                CloseDB(conn);
                if (null == dt)
                    throw new Exception("未知错误！");
                return dt;
            }
            catch (Exception)
            {
                CloseDB(conn);
                throw;
            }

        }
        #endregion
        #region 执行命令
        public void Exec(string sqlstr, SQLiteParameter[] op = null)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                var conn = ConnDB();
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand(sqlstr, conn);
                    if (op != null)
                    {
                        CheckParameter(ref op);
                        cmd.Parameters.AddRange(op);
                    }
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    CloseDB(conn);
                }
                catch (Exception)
                {
                    CloseDB(conn);
                    throw;
                }

            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion
        #region 执行事务
        public void ExecTrans(string sqlstr, SQLiteParameter[][] op)
        {
            var conn = ConnDB();
            SQLiteTransaction trs = conn.BeginTransaction();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand
                {
                    CommandText = sqlstr,
                    Connection = conn
                };
                for (int i = 0; i < op.Length; i++)
                {
                    cmd.Parameters.AddRange(op[i]);
                    cmd.ExecuteNonQuery();
                }
                trs.Commit();
                cmd.Dispose();
                CloseDB(conn);
            }
            catch (Exception)
            {
                trs.Rollback();
                CloseDB(conn);
                throw;
            }



        }
        #endregion
    }
}
